package comm.hg.db;

import comm.hg.bean.FaPiaoLei;
import comm.hg.bean.GongYingShangLei;
import comm.hg.bean.KuCunLei;
import comm.hg.bean.ShangPinLei;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQL_HuaGong {
    //ComboPooledDataSource   c3p0数据源（表示是一个连接池）
    private static Connection dataSource = null;

    /**
     * 获取连接池对象
     */
    public static Connection getDataSource() {
        if(dataSource==null){

            //加载驱动
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String url="jdbc:mysql://127.0.0.1:3306/huagong?characterEncoding=UTF-8";
                String user="root";
                String password="123456";

                //创建数据连接对象
                dataSource= DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                System.out.println("数据库连接异常");
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("数据库连接异常");
            }
        }

        return dataSource;
    }

    /**
     * 查询所有信息
     * @throws SQLException
     */
    public static List<GongYingShangLei> select() throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select a.gid,b.user_name,a.`name`,a.phone,a.wechat,a.`status`,a.create_time,a.remark from supplier a,users b where a.user_id=b.uid ";
        List<GongYingShangLei> c = qr.query(getDataSource(),sql,new  BeanListHandler<GongYingShangLei>(GongYingShangLei.class));

//        for (GongYingShangLei g:c) {
//            //System.out.println(g.getGid());
//            int b = Integer.valueOf(g.getGid()).intValue();
//           String a=String.format("AH%05d",b);
//           g.setGid (a);
//        }
//        c=c.stream().map(b->{
//            b.setGid(String.format("AH%05d", b.getGid()));
//            return  b;
//        }).collect(Collectors.toList());
        return c;
    }

    /**
     * 通过gid查询
     * @param gid
     * @return
     * @throws SQLException
     */
    public static GongYingShangLei selectId(String gid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select name,phone,wechat,remark from supplier where gid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(GongYingShangLei.class),gid);


    }

    /**
     * 联合查询
     * @param name
     * @param uid
     * @return
     * @throws SQLException
     */
    public static List<GongYingShangLei> selectByid(String name,String uid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select a.gid,b.user_name,a.`name`,a.phone,a.wechat,a.`status`,a.create_time,a.remark from supplier a,users b where a.user_id=b.uid ";
        ArrayList arr=new ArrayList();

        if(!"".equals(name) && name != null){
          sql+=" and a.name=?";
          arr.add(name);

        }
        if(!"".equals(uid) && uid != null){
            sql+=" and b.user_name=?";
            arr.add(uid);
        }
        List<GongYingShangLei> c =qr.query(getDataSource(),sql, new BeanListHandler<GongYingShangLei>(GongYingShangLei.class),arr.toArray());
//        for (GongYingShangLei category : c) {
////            System.out.println(category.getName());
////        }
          return c;
    }

    /**
     * 商品查询所有信息
     * @throws SQLException
     */
    public static List<ShangPinLei> shangPingSelect() throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select b.sid,b.name,b.cas,b.price_info,c.user_name,b.supplier_id,b.update_time from inventory a left join  commodity b on a.cas=b.cas left join users c on b.user_id=c.uid";
        List<ShangPinLei> c = qr.query(getDataSource(),sql, new BeanListHandler<ShangPinLei>(ShangPinLei.class));
//        for (ShangPinLei g:c) {
//            //System.out.println(g.getSid());
//            int b = Integer.valueOf(g.getSid()).intValue();
//            String a=String.format("YY%05d",b);
//            g.setSid(a);
//        }
        return c;
    }

    /**
     * 商品模糊联合查询
     * @param name
     * @param cas
     * @return
     * @throws SQLException
     */
    public static List<ShangPinLei> shangPinSelectMuHu(String name,String cas,String riQi,String riQi1) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select b.sid,b.name,b.cas,b.price_info,c.user_name,b.supplier_id,b.update_time,a.amount,b.img_status,b.file_status from commodity b left join inventory a on a.cas=b.cas left join users c on b.user_id=c.uid where 1=1";
        ArrayList arr=new ArrayList();

        if(!"".equals(name) && name != null){
            sql+=" and b.name LIKE ?";
            arr.add("%"+ name +"%");

        }
        if(!"".equals(cas) && cas != null){
            sql+=" and b.cas LIKE ?";
            arr.add("%"+cas+"%");
        }
        if(riQi != null && riQi1 != null && !"".equals(riQi) && !"".equals(riQi1)){
            sql+=" and b.create_time between ? and ?";
            arr.add(riQi);
            arr.add(riQi1);
        }
        if(1==1){
            sql+=" order by a.amount desc";
        }

        List<ShangPinLei> c =qr.query(getDataSource(),sql, new BeanListHandler<>(ShangPinLei.class),arr.toArray());
//        /        for (ShangPinLei g:c) {
////            //System.out.println(g.getSid());
////            int b = Integer.valueOf(g.getSid()).intValue();
////            String a=String.format("YY%05d",b);
////            g.setSid(a);
////        }
        return c;
    }

    /**
     * 商品 查找供应商
     * @param
     * @return
     * @throws SQLException
     */
    public static List<ShangPinLei> shangPinSelectName(String name) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select gid,name from supplier where 1=1 ";
        ArrayList arr=new ArrayList();

        if(!"".equals(name)){
            sql+=" and name LIKE ?";
            arr.add("%"+ name +"%");

        }
        List<ShangPinLei> c = qr.query(getDataSource(),sql, new BeanListHandler<ShangPinLei>(ShangPinLei.class),arr.toArray());
        return c;
    }

    /**
     * 商品 查找供应商
     * @param
     * @return
     * @throws SQLException
     */
    public static List<ShangPinLei> shangPinSelectName1() throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select gid,name from supplier";

        List<ShangPinLei> c = qr.query(getDataSource(),sql, new BeanListHandler<ShangPinLei>(ShangPinLei.class));
        return c;
    }

    /**
     * 通过gid查询 商品 供应商名称和电话
     * @param gid
     * @return
     * @throws SQLException
     */
    public static ShangPinLei SHangPingSelectId(String gid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select name,phone from supplier where gid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(ShangPinLei.class),gid);
    }

    /**
     * 商品修改文本框放值
     * @param sid
     * @return
     * @throws SQLException
     */
    public static ShangPinLei SHangPingSXIuGaiWendyBenFuZhi(String sid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select name,cas,price_info,commodity_info from commodity where sid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(ShangPinLei.class),sid);
    }

    /**
     * 查看供应商文本框放值
     * @param sid
     * @return
     * @throws SQLException
     */
    public static ShangPinLei SHangPingChaKanGongYingShang(String sid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select name,cas,create_time,update_time,price_info,commodity_info,img_path from commodity where sid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(ShangPinLei.class),sid);


    }

    /**
     * 查询cas号和供应商id
     * @param cas
     * @param supplier_id
     * @return
     * @throws SQLException
     */
    public static ShangPinLei SHangPingSelectCasId(String cas,String supplier_id) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select cas,supplier_id from commodity WHERE cas=? and supplier_id=?";
        ShangPinLei c=qr.query(getDataSource(),sql, new BeanHandler<>(ShangPinLei.class),cas,supplier_id);
        return c;
    }


    /**
     * div  放图片
     * @param sid
     * @return
     * @throws SQLException
     */
    public static ShangPinLei SHangPingDivTuPian(String sid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select img_path from commodity where sid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(ShangPinLei.class),sid);
    }

    /**
     * div  放文件
     * @param sid
     * @return
     * @throws SQLException
     */
    public static ShangPinLei SHangPingDivWenJian(String sid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select file_path from commodity where sid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(ShangPinLei.class),sid);
    }

    /**
     * 库存查询全部 加模糊
     * @param name
     * @param cas
     * @param number
     * @return
     * @throws SQLException
     */
    public static List<KuCunLei> selectKuCun(String name,String cas,String number) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select * from inventory where 1=1";
        ArrayList arr=new ArrayList();

        if(!"".equals(name) && name != null){
            sql+=" and name LIKE ?";
            arr.add("%"+ name +"%");

        }
        if(!"".equals(cas) && cas != null){
            sql+=" and cas LIKE ?";
            arr.add("%"+cas+"%");
        }
        if(!"".equals(number) && number != null){
            sql+=" and number LIKE ?";
            arr.add("%"+number+"%");
        }
        List<KuCunLei> c =qr.query(getDataSource(),sql, new BeanListHandler<>(KuCunLei.class),arr.toArray());
//        /        for (ShangPinLei g:c) {
////            //System.out.println(g.getSid());
////            int b = Integer.valueOf(g.getSid()).intValue();
////            String a=String.format("YY%05d",b);
////            g.setSid(a);
////        }
        return c;
    }

    /**
     * 查询0库存
     * @return
     * @throws SQLException
     */
    public static List<KuCunLei> selectKuCunLing() throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select * from inventory where amount='0'";
        return qr.query(getDataSource(),sql, new BeanListHandler<>(KuCunLei.class));
    }

    /**
     * 库存  修改文本框放值
     * @param kid
     * @return
     * @throws SQLException
     */
    public static KuCunLei kuCunXiuGaiId(String kid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select * from inventory where kid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(KuCunLei.class),kid);
    }

    /**
     * 发票查询 模糊查询
     * @param cas
     * @param name
     * @return
     * @throws SQLException
     */
    public static List<FaPiaoLei> selectFaPiao(String cas, String name) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select * from invoice where 1=1";
        ArrayList arr=new ArrayList();

        if(!"".equals(cas) && cas != null){
            sql+=" and cas LIKE ?";
            arr.add("%"+cas+"%");
        }
        if(!"".equals(name) && name != null){
            sql+=" and name LIKE ?";
            arr.add("%"+ name +"%");

        }
        List<FaPiaoLei> c =qr.query(getDataSource(),sql, new BeanListHandler<>(FaPiaoLei.class),arr.toArray());
               for (FaPiaoLei g:c) {
            //System.out.println(g.getSid());
            int b = Integer.valueOf(g.getSid()).intValue();
            String a=String.format("AH%05d",b);
            g.setZid(a);
       }
        return c;
    }


    /**
     * 发票 修改 文本框放值
     * @param sid
     * @return
     * @throws SQLException
     */
    public static FaPiaoLei  faPiaoXiuGaiFuZhi(String sid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select * from invoice where sid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(FaPiaoLei.class),sid);
    }


    /**
     * 库存 修改 文本框放值
     * @param kid
     * @return
     * @throws SQLException
     */
    public static KuCunLei kuCunXiuGaiFuZhi(String kid) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询
        String sql = "select * from inventory where kid=?";
        return qr.query(getDataSource(),sql, new BeanHandler<>(KuCunLei.class),kid);
    }



    /**
     * 增加 数据   增删改
     * @param sql
     * @param params
     * @throws SQLException
     */
    public static void updateShuJu(String sql,Object... params) throws SQLException{
        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner();
        // 2.执行查询

        int number = qr.update(getDataSource(),sql,params);
        // 3.List
        System.out.println(number);
    }



    public static void main(String[] args) {


    }
}
